package com.zyd.service.vehicle.impl;

import com.alibaba.fastjson.JSON;
import com.google.common.base.Splitter;
import com.google.common.collect.Maps;
import com.zyd.constants.ErrorDef;
import com.zyd.context.HeaderContext;
import com.zyd.dao.SocketNowBlackCarDao;
import com.zyd.dao.VehicleDao;
import com.zyd.exception.BusinessException;
import com.zyd.io.model.*;
import com.zyd.io.page.OperateKey;
import com.zyd.io.page.PageBean;
import com.zyd.io.page.SearchKey;
import com.zyd.io.vo.page.MultiQueryPageVO;
import com.zyd.io.vo.vehicle.*;
import com.zyd.mappers.BlackCarEntityMapper;
import com.zyd.mappers.SafeEntityMapper;
import com.zyd.mappers.VehicleEntityMapper;
import com.zyd.service.meal.MealService;
import com.zyd.service.power.PowerService;
import com.zyd.service.setmeal.SetMealService;
import com.zyd.service.vehicle.VehicleService;
import com.zyd.service.wechat.WeChatService;
import com.zyd.service.wechat.impl.WeChatServiceImpl;
import com.zyd.util.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static javax.xml.bind.JAXBIntrospector.getValue;

@Component
public class VehicleServiceImpl implements VehicleService {

    private static final Splitter SPLITTER = Splitter.on(',').trimResults().omitEmptyStrings();

    private static final Logger LOGGER = LoggerFactory.getLogger(VehicleServiceImpl.class);

    @Autowired
    private VehicleEntityMapper mapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private MealService mealService;

    @Autowired
    private SetMealService service;

    @Autowired
    private PowerService powerService;

    @Autowired
    private BlackCarEntityMapper blackCarEntityMapper;

    @Autowired
    private SocketNowBlackCarDao socketNowBlackCarDao;

    @Override
    public PageBean<VehicleEntity> findVehiclePage(MultiQueryPageVO request) {
        String countSql = "select count(1) ";
        String selectFields = "select * ";
        StringBuilder sql = new StringBuilder();
        sql.append("from zydzn_vehicle where dr = 0 ");
        sql.append(SqlUtils.addMultiCondition(request.getSearchKeys()));

        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId((String) HeaderContext.get().get("userid"), "2010");
        if (rolePowerEntity != null && !org.springframework.util.StringUtils.isEmpty(rolePowerEntity.getArea())) {
            sql.append(" and newareaid in (" + SqlUtils.addIn(rolePowerEntity.getArea()) + ")");
        } else {
            return null;
        }
        String sqlStr = sql.toString().replaceAll("endtime", "keeptime");
        Long totalRows = jdbcTemplate.queryForObject(countSql + sqlStr, Long.class);
        String pageSql = SqlUtils.addPage(selectFields + sqlStr + " ORDER BY keeptime desc ", request.getCurrentPage(), request.getPageSize());

        List<VehicleEntity> ret = jdbcTemplate.query(pageSql, new RowMapper<VehicleEntity>() {

            @Override
            public VehicleEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                VehicleEntity vehicleEntity = SqlUtils.mappingToBean(rs, VehicleEntity.class);
                MealEntity lastMeal = mealService.findLastMealByVeId(vehicleEntity.getId());

                //套餐信息应该存在
                if (lastMeal != null) {
                    //复制属性
                    BeanUtils.copyProperties(lastMeal, vehicleEntity, "id");
                }


                return vehicleEntity;
            }
        });

        PageBean<VehicleEntity> pageBean = new PageBean<VehicleEntity>(request.getCurrentPage(), request.getPageSize(), totalRows.intValue());
        pageBean.setItems(ret);
        return pageBean;

    }

    @Override
    public List<VehicleEntity> findVehicleList(String userid) {
        String selectFields = "select *";
        StringBuilder sql = new StringBuilder();
        sql.append(" from zydzn_vehicle where dr = 0");
        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId(userid, "2010");
        if (rolePowerEntity != null) {
            sql.append(" and newareaid in (" + SqlUtils.addIn(rolePowerEntity.getArea()) + ")");
        } else {
            sql.append(" and newareaid in ('10000')");
        }
        List<VehicleEntity> ret = jdbcTemplate.query(selectFields + sql.toString(), new RowMapper<VehicleEntity>() {

            @Override
            public VehicleEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                VehicleEntity vehicleEntity = SqlUtils.mappingToBean(rs, VehicleEntity.class);
                MealEntity lastMeal = mealService.findLastMealByVeId(vehicleEntity.getId());

                //套餐信息应该存在
                if (lastMeal != null) {
                    //复制属性
                    BeanUtils.copyProperties(lastMeal, vehicleEntity, "id");
                    vehicleEntity.setAccident(lastMeal.getAccident());
                }


                return vehicleEntity;
            }
        });

        return ret;
    }

    @Override
    public VehicleEntity findVehicle(String id) {
        VehicleEntityKey key = new VehicleEntityKey();
        key.setId(id);
        VehicleEntity entity = mapper.selectByPrimaryKey(key);
        MealEntity lastMeal = mealService.findLastMealByVeId(entity.getId());
        if (lastMeal != null) {
            //复制属性
            BeanUtils.copyProperties(lastMeal, entity, "id");
            entity.setAccident(lastMeal.getAccident());
        }

        return entity;
    }

    @Override
    public void addVehicle(VehicleEntity vehicle) {
        vehicle.setId(UUIDUtil.getUUid());

        checkDataValidation(vehicle);
        checkValid(vehicle);

        vehicle.setTs(ToolUtils.getNowTime());
        vehicle.setDr(0);
        // 新增创建人
        vehicle.setCreateid("");

        SafeEntity meal = service.findMeal(vehicle.getMeal_id());
        System.out.println("新增车辆第一张图片" + vehicle.getImg1());
        System.out.println("新增车辆第二张图片" + vehicle.getImg2());
        System.out.println("新增车辆第三张图片" + vehicle.getImg3());

        MealEntity entity = new MealEntity();
        entity.setAccident(vehicle.getAccident());
        entity.setThirdduty(vehicle.getThirdduty());
        entity.setTheftdutp(vehicle.getTheftdutp());
        entity.setStart_time(vehicle.getStart_time());
        entity.setEnd_time(vehicle.getEnd_time());
        entity.setId(UUIDUtil.getUUid());
        entity.setCreate_time(ToolUtils.getNowTime());
        entity.setHas_accient(meal.getHas_accient());
        entity.setHas_thirdduty(meal.getHas_third());
        entity.setHas_theftdutp(meal.getHas_theft());
        entity.setMeal_money(meal.getSalemny());
        entity.setMeal_name(meal.getSalename());
        entity.setVehicle_id(vehicle.getId());
        entity.setMeal_id(meal.getId());

        mapper.insertSelective(vehicle);
        mealService.addMeal(entity);
    }

    @Override
    public void updateVehicle(VehicleEntity vehicleEntity) {
        checkDataValidation(vehicleEntity);

        vehicleEntity.setTs(ToolUtils.getNowTime());
        //checkValid(vehicleEntity);
        mapper.updateByPrimaryKeySelective(vehicleEntity);

        MealEntity lastMeal = mealService.findLastMealByVeId(vehicleEntity.getId());

        SafeEntity meal = service.findMeal(vehicleEntity.getMeal_id());

        lastMeal.setMeal_id(vehicleEntity.getMeal_id());
        lastMeal.setMeal_name(vehicleEntity.getMeal_name());
        lastMeal.setMeal_money(meal.getSalemny());
        lastMeal.setStart_time(vehicleEntity.getStart_time());
        lastMeal.setEnd_time(vehicleEntity.getEnd_time());
        lastMeal.setAccident(vehicleEntity.getAccident());
        lastMeal.setTheftdutp(vehicleEntity.getTheftdutp());
        lastMeal.setThirdduty(vehicleEntity.getThirdduty());

        mealService.updateMeal(lastMeal);


    }

    @Override
    public void deleteVehicle(String id) {
        VehicleEntity vehicleEntity = new VehicleEntity();
        vehicleEntity.setId(id);
        vehicleEntity.setDr(1);
        vehicleEntity.setTs(ToolUtils.getNowTime());
        mapper.updateByPrimaryKeySelective(vehicleEntity);
    }

    @Cacheable("vehicleTrack")
    @Override
    public List<VehicleTrackResVO> findVehicleTrack(VehicleTrackReqVO request) {
        LOGGER.info("开始查询轨迹,请求" + JSON.toJSONString(request));

        /**
         * 最终的sql
         SELECT a.stationcode,a.jd,a.wd,a.address,a.stationname,b.tag_id,b.rec_time as aftertime  FROM  zydzn_station a  left JOIN socket_now b  on a.stationcode = b.base_id   where  b.rec_time >= '2018-11-9 00:00:01'   and b.rec_time <= '2018-12-29 00:00:00'  and a.dr=0  and  b.tag_id = '6800000C3D' ORDER BY b.rec_time limit 0,1000
         采用预编译 数据库会存储预编译的sql 直接执行
         */
        String tagcode = request.getTagcode();
        String sTime = request.getStartime();
        String eTime = request.getEndtime();
        BlackCarEntityExample example = new BlackCarEntityExample();
        BlackCarEntityExample.Criteria criteria = example.createCriteria();
        criteria.andTagcodeEqualTo(tagcode).andDrEqualTo(0).andStateEqualTo(0);

        List<BlackCarEntity> blackCarEntities = blackCarEntityMapper.selectByExample(example);
        if (blackCarEntities != null && blackCarEntities.size() >= 1) {//如果查询出的黑车数据不为空
            //从黑车轨迹表查询数据
            return socketNowBlackCarDao.findTrack(tagcode, sTime, eTime);
        }
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date starTime = null;
        Date endTime = null;
        try {
            starTime = format.parse(sTime);
            endTime = format.parse(eTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        /**
         * 查询sql
         */
        String sql = "select tag_id,DATE_FORMAT(rec_time,'%Y-%m-%d %H:%i:%s') aftertime,base_id from socket_now where rec_time >=? and rec_time <=? and tag_id =?  ORDER BY rec_time ";
        /**
         * 分组sql先查出基站编号,拿基站编号去查询出对应的经纬度
         * 这里是为了将对db的io操作降到最低
         */
        String groupSql = "select base_id from socket_now where rec_time >= ? and rec_time <= ? and tag_id =?  GROUP BY base_id ";
        List<String> baseIds = jdbcTemplate.queryForList(groupSql, String.class, starTime, endTime, request.getTagcode());
        /**
         * 基站编号为key,对应数据为值
         */
        Map<String, VehicleTrackResVO> vos = new HashMap<>();
        for (String baseId : baseIds) {
            try {
                VehicleTrackResVO vehicleTrackResVO = jdbcTemplate.queryForObject("select stationcode,jd,wd,address,stationname from zydzn_station where dr=0 and stationcode= ? limit 1 ", new BeanPropertyRowMapper<>(VehicleTrackResVO.class), baseId);
                vos.put(baseId, vehicleTrackResVO);
            } catch (DataAccessException e) {
                LOGGER.info("该基站不存在:" + baseId);
            }
        }
        String vinno = null;
        try {
            vinno = jdbcTemplate.queryForObject("select vinno from zydzn_vehicle where tagcode = ? limit 1", String.class, tagcode);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        String finalVinno = vinno;
        List<VehicleTrackResVO> res = jdbcTemplate.query(sql, (resultSet, i) -> {
            /**
             * 根据基站编号去map中找经纬度
             */
            String base_id = resultSet.getString("base_id");
            VehicleTrackResVO ret = new VehicleTrackResVO();
            VehicleTrackResVO resVO = vos.get(base_id);
            if (resVO != null) {
                BeanUtils.copyProperties(resVO, ret);
                ret.setAftertime(resultSet.getString("aftertime"));
                ret.setVinno(finalVinno);
            }
            return ret;
        }, starTime, endTime, request.getTagcode());
        res.removeAll(Collections.singleton(null));//删除集合中为空的元素
        LOGGER.info("查询轨迹结束");
        Collections.sort(res);
        return res;
    }


    @Override
    public PageBean<VehicleRecordResVO> findVehicleRecord(VehicleRecordReqVO request) {
        String countsql = " SELECT count(*) ";
        String selectfield = " SELECT b.carname,a.tagcode, a.stationcode,c.stationname,DATE_FORMAT(a.regtime,'%Y-%m-%d %H:%i:%s') as regtime ";
        StringBuffer sql = new StringBuffer();
        sql.append("  from zydzn_vehiclerecord a LEFT JOIN zydzn_vehicle b on a.tagcode=b.tagcode and b.dr=0 ");
        sql.append(" LEFT JOIN zydzn_station c on a.stationcode= c.stationcode and c.dr=0 ");
        sql.append(" where  a.dr=0 and a.tagcode is not null and  a.stationcode is not null  ");
        sql.append("  and a.regtime between  ");
        sql.append("  DATE_FORMAT('" + request.getStartTime() + "', '%Y-%m-%d %H:%i:%s') ");
        sql.append("  and DATE_FORMAT('" + request.getEndTime() + "', '%Y-%m-%d %H:%i:%s') ");

        if (!StringUtils.isBlank(request.getSafenum())) {
            sql.append("  and  a.tagcode = '" + request.getSafenum() + "'");
        }
        if (!StringUtils.isBlank(request.getStationCode())) {
            sql.append("  and a.stationcode = '" + request.getStationCode() + "'");
        }
        if (StringUtils.isNotBlank(request.getName())) {
            sql.append(" and (c.stationname like '%" + request.getName() + "%' or b.carname like '%" + request.getName() + "%') ");
        }
        sql.append(" ORDER BY a.regtime desc  ");

        Long count = jdbcTemplate.queryForObject(countsql + sql.toString(), Long.class);

        String pageSql = SqlUtils.addPage(selectfield + sql.toString(), request.getCurrentPage(), request.getPageSize());

        List<VehicleRecordResVO> ret = jdbcTemplate.query(pageSql, new RowMapper<VehicleRecordResVO>() {
            @Override
            public VehicleRecordResVO mapRow(ResultSet resultSet, int i) throws SQLException {
                VehicleRecordResVO ret = new VehicleRecordResVO();
                ret.setCarname(resultSet.getString("carname"));//车名
                ret.setSafenum(resultSet.getString("tagcode"));//标签代码
                ret.setStationCode(resultSet.getString("stationcode"));//站代码
                ret.setStationName(resultSet.getString("stationname"));//站名
                ret.setTime(resultSet.getString("regtime"));//注册时间
                return ret;
            }
        });

        PageBean<VehicleRecordResVO> pageBean = new PageBean<VehicleRecordResVO>(request.getCurrentPage(), request.getPageSize(), count.intValue());
        pageBean.setItems(ret);
        return pageBean;
    }

    //    微信小程序费用统计
    @Override
    public PageBean<Map<String, Object>> countFees(FeeReqVO request) {
        String str = "SELECT a.create_time,a.status,a.money,b.carname,b.mobile,b.vehiclebrand,b.tagcode,b.vinno  FROM zydzn_safe_order a left JOIN  zydzn_vehicle b ON a.vehicle_id =b.id WHERE 1=1 ";
        if (request.getCarname() != null && !request.getCarname().equals("")) {
            str += " and  carname LIKE  '%" + request.getCarname() + "%'";
        }

        String countMoneySql = "SELECT SUM(a.money) FROM zydzn_safe_order a LEFT JOIN zydzn_vehicle b on a. vehicle_id=b.id WHERE 1=1 AND status=1 ";
        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId((String) HeaderContext.get().get("userid"), "2010");

        if (rolePowerEntity != null && !org.springframework.util.StringUtils.isEmpty(rolePowerEntity.getArea())) {
            str += " and b.newareaid in (" + SqlUtils.addIn(rolePowerEntity.getArea()) + ")";
        } else {
            return null;
        }

        String countSql = "select count(1) from (" + str + ") as b";
        Double countMoney = jdbcTemplate.queryForObject(countMoneySql, Double.class);

        Long totalRows = jdbcTemplate.queryForObject(countSql, Long.class);

        String pageSql = SqlUtils.addPage(str, request.getCurrentPage(), request.getPageSize());
        List<Map<String, Object>> ret = jdbcTemplate.query(pageSql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> ret = Maps.newHashMap();
                ret.put("carname", rs.getString("carname"));
                ret.put("mobile", rs.getString("mobile"));
                ret.put("vehiclebrand", rs.getString("vehiclebrand"));
                ret.put("tagcode", rs.getString("tagcode"));
                ret.put("vinno", rs.getString("vinno"));
                ret.put("create_time", rs.getString("create_time"));
                ret.put("status", rs.getString("status"));
                Double money = rs.getDouble("money");
                ret.put("money", money / 100);


                return ret;
            }
        });

        PageBean<Map<String, Object>> pageBean = new PageBean<Map<String, Object>>(request.getCurrentPage(), request.getPageSize(), totalRows.intValue());
        pageBean.setItems(ret);
        pageBean.getAttributes().put("countFee", countMoney / 100);
        return pageBean;
    }


    private void checkValid(VehicleEntity vehicle) {
        VehicleEntityExample entityExample = new VehicleEntityExample();
        entityExample.or().andTagcodeEqualTo(vehicle.getTagcode()).andDrEqualTo(0);
        long count = mapper.countByExample(entityExample);

        if (count > 0) {
            throw new BusinessException(ErrorDef.SYSTEM_ERROR, "防盗芯片已存在!");
        }

//        entityExample = new VehicleEntityExample();
//        entityExample.or().andMobileEqualTo(vehicle.getMobile()).andDrEqualTo(0);
//        count = mapper.countByExample(entityExample);
//        if (count > 0) {
//            throw new BusinessException(ErrorDef.SYSTEM_ERROR, "手机号码已存在!");
//        }

        entityExample = new VehicleEntityExample();
        entityExample.or().andTheftnumEqualTo(vehicle.getTheftnum()).andDrEqualTo(0);
        count = mapper.countByExample(entityExample);
        if (count > 0) {
            throw new BusinessException(ErrorDef.SYSTEM_ERROR, "车架号已存在!");
        }
    }

    private String checkErrorMsg(VehicleEntity vehicle) {
        String errmsg = null;
        if (StringUtils.isEmpty(vehicle.getCarname())) {
            errmsg = "车主姓名不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getMobile())) {
            errmsg = "车主手机号码不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getTheftnum())) {
            errmsg = "防盗号牌不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getTagcode())) {
            errmsg = "防盗芯片不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getVehiclebrand())) {
            errmsg = "品牌不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getCarcolour())) {
            errmsg = "车辆颜色不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getTheftdutp())) {
            errmsg = "防盗险不能为空!";
            return errmsg;
        }
        if (StringUtils.isEmpty(vehicle.getNewareaid())) {
            errmsg = "区县/辖区不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getReger())) {
            errmsg = "登记人不能为空!";
            return errmsg;
        } else if (StringUtils.isEmpty(vehicle.getSalesite())) {
            errmsg = "安装网点不能为空!";
            return errmsg;
        } else if (!ValidationUtil.isPhoneNumber(vehicle.getMobile())) {
            errmsg = "手机号格式不正确";
        } else if (!ValidationUtil.isIdCard(vehicle.getCarid())) {
            errmsg = "身份证号格式不正确";
        }
        return errmsg;
    }

    private void checkDataValidation(VehicleEntity vehicle) {
        String validation = checkErrorMsg(vehicle);
        if (StringUtils.isNotBlank(validation)) {
            throw new BusinessException(ErrorDef.SYSTEM_ERROR, validation);
        }
    }

    //登记车辆统计

    public PageBean<Map<String, Object>> findRegisterVehiclePage(MultiQueryPageVO request) {
        String countSql = "select count(1) from ";
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT reger,count(1) as num FROM zydzn_vehicle where dr = 0");
        sql.append(SqlUtils.addMultiCondition(request.getSearchKeys()));
        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId((String) HeaderContext.get().get("userid"), "2010");
        if (rolePowerEntity != null) {
            sql.append(" and newareaid in (" + SqlUtils.addIn(rolePowerEntity.getArea()) + ")");
        } else {
            sql.append(" and newareaid in ('10000')");
        }
        sql.append("GROUP BY reger");

        Long totalRows = jdbcTemplate.queryForObject(countSql + "(" + sql.toString() + ")as a", Long.class);
        String pageSql = SqlUtils.addPage(sql.toString(), request.getCurrentPage(), request.getPageSize());

        List<Map<String, Object>> ret = jdbcTemplate.query(pageSql, new RowMapper<Map<String, Object>>() {

            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> ret = Maps.newHashMap();
                ret.put("reger", rs.getString("reger"));
                ret.put("num", rs.getString("num"));

                return ret;
            }
        });
        PageBean<Map<String, Object>> pageBean = new PageBean<Map<String, Object>>(request.getCurrentPage(), request.getPageSize(), totalRows.intValue());
        pageBean.setItems(ret);
        return pageBean;

    }

    @Override
    public List<VehicleEntity> findVehicleByReger(String reger) {
        if (org.springframework.util.StringUtils.isEmpty(reger)) {
            throw new BusinessException("传参错误");
        }
        VehicleEntityExample example = new VehicleEntityExample();
        VehicleEntityExample.Criteria criteria = example.createCriteria();
        criteria.andRegerEqualTo(reger);
        return mapper.selectByExample(example);
    }

    public List<VehicleEntity> findVehicleList(MultiQueryPageVO request, String userid) {

        String selectFields = "select * ";
        StringBuilder sql = new StringBuilder();
        sql.append("from zydzn_vehicle where dr = 0");
        sql.append(SqlUtils.addMultiCondition(request.getSearchKeys()));

        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId(userid, "2010");
        if (rolePowerEntity != null && !org.springframework.util.StringUtils.isEmpty(rolePowerEntity.getArea())) {

            sql.append(" and newareaid in (" + SqlUtils.addIn(rolePowerEntity.getArea()) + ") ORDER BY keeptime desc");
        } else {
            return null;
        }
        String sqlStr = sql.toString().replaceAll("endtime", "keeptime");
        List<VehicleEntity> ret = jdbcTemplate.query(selectFields + sqlStr, (rs, rowNum) -> {
            VehicleEntity vehicleEntity = SqlUtils.mappingToBean(rs, VehicleEntity.class);
            MealEntity lastMeal = mealService.findLastMealByVeId(vehicleEntity.getId());
            if (lastMeal != null) {
                BeanUtils.copyProperties(lastMeal, vehicleEntity, "id");
            }
            return vehicleEntity;
        });


        return ret;
    }

    //锁车查询
    @Override
    public PageBean<Map<String, Object>> appletFindPage(MultiQueryPageVO request) {
        String str = "select  b.*,a.start_time FROM zydzn_car_lock a LEFT JOIN zydzn_vehicle b on a.tagcode = b.tagcode WHERE a.dr=0 ";
        String pageSqlseach = str + disposeSql(request.getSearchKeys());

        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId((String) HeaderContext.get().get("userid"), "2010");

        if (rolePowerEntity != null && !org.springframework.util.StringUtils.isEmpty(rolePowerEntity.getArea())) {
            pageSqlseach += " and b.newareaid in (" + SqlUtils.addIn(rolePowerEntity.getArea()) + ")";
        } else {
            return null;
        }
        String countSql = "select count(1) from (" + pageSqlseach + ") as b";
        Long totalRows = jdbcTemplate.queryForObject(countSql, Long.class);
        String pageSql = SqlUtils.addPage(pageSqlseach, request.getCurrentPage(), request.getPageSize());
        List<Map<String, Object>> ret = jdbcTemplate.query(pageSql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> ret = Maps.newHashMap();
                ret.put("carname", rs.getString("carname"));
                ret.put("mobile", rs.getString("mobile"));
                ret.put("vinno", rs.getString("vinno"));
                ret.put("vehiclebrand", rs.getString("vehiclebrand"));
                ret.put("tagcode", rs.getString("tagcode"));
                ret.put("islock", rs.getString("islock"));
                ret.put("start_time", rs.getString("start_time"));

                return ret;
            }
        });

        PageBean<Map<String, Object>> pageBean = new PageBean<Map<String, Object>>(request.getCurrentPage(), request.getPageSize(), totalRows.intValue());
        pageBean.setItems(ret);
        return pageBean;
    }

    /**
     * 查询审核车辆
     *
     * @param request
     * @return
     */
    @Override
    public PageBean examineVehicleList(MultiQueryPageVO request) {
        String countSql = "SELECT COUNT(1) ";
        String selectFields = "SELECT * ";
        StringBuilder sql = new StringBuilder();
        sql.append("FROM zydzn_vehicle_examine WHERE state = 0  ");
        sql.append(SqlUtils.addMultiCondition(request.getSearchKeys()));

        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId((String) HeaderContext.get().get("userid"), "2010");
        if (rolePowerEntity != null && !org.springframework.util.StringUtils.isEmpty(rolePowerEntity.getArea())) {
            sql.append(" AND newareaid IN (" + SqlUtils.addIn(rolePowerEntity.getArea()) + ")");
        } else {
            return null;
        }
        String sqlStr = sql.toString();
        Long totalRows = jdbcTemplate.queryForObject(countSql + sqlStr, Long.class);
        String pageSql = SqlUtils.addPage(selectFields + sqlStr + " ORDER BY createtime DESC ", request.getCurrentPage(), request.getPageSize());

        List<Map<String, Object>> ret = jdbcTemplate.queryForList(pageSql);

        PageBean<Map<String, Object>> pageBean = new PageBean<>(request.getCurrentPage(), request.getPageSize(), totalRows.intValue());
        pageBean.setItems(ret);
        return pageBean;

    }

    @Autowired
    private WeChatServiceImpl weChatService;

    @Autowired
    private VehicleDao vehicleDao;

    /**
     * 修改车辆审核状态
     *
     * @param id
     * @param state 改为1为审核通过  改为2为审核未通过
     */
    @Override
    public void updateExamineVehicle(String id, Integer state) {
        if (state == 1) {
            weChatService.auditPass(id, state);
        } else {
            vehicleDao.updateExamineVehicleState(id, state);
        }
    }

    @Override
    public void deleteExamine(String id) {
        vehicleDao.updateExamineVehicleState(id, 4);
    }

    public String disposeSql(List<SearchKey> searchKeys) {
        StringBuilder sql = new StringBuilder();
        for (SearchKey searchKey : searchKeys) {
            sql.append("  and b. " + searchKey.getName() + " " + searchKey.getOperate() + " ");
            if (searchKey.getOperate().equalsIgnoreCase(OperateKey.LIKE)) {
                sql.append("\'" + SqlUtils.andLike(searchKey.getValue()) + "\'");
            } else {
                //支持 =、>、<、!=、like、in、notin
                sql.append("\'" + searchKey.getValue() + "\'");
            }
        }
        return sql.toString();
    }

    /**
     * 导入数据
     *
     * @param workbook
     */
    @Override
    public void importData(Workbook workbook) {
        HashMap<String, String> hashMap = new HashMap<>();
        hashMap.put("白色", "white");
        hashMap.put("黑色", "black");
        hashMap.put("红色", "red");
        hashMap.put("橙色", "orange");
        hashMap.put("黄色", "yellow");
        hashMap.put("蓝色", "blue");
        hashMap.put("粉色", "pink");
        hashMap.put("棕色", "brown");
        hashMap.put("银灰色", "silverGray");
        hashMap.put("银白色", "silverWhite");
        hashMap.put("紫色", "violet");
        hashMap.put("金色", "golden");
        hashMap.put("绿色", "green");
        hashMap.put("其他", "other");

        HashMap<String, String> type = new HashMap<>();
        type.put("两轮", "lun2");
        type.put("二轮", "lun2");
        type.put("三轮", "lun3");
        type.put("四轮", "lun4");
        type.put("摩托", "motor");
        type.put("其他", "other");

        HashMap<Object, Integer> is = new HashMap<>();
        is.put("是", 0);
        is.put("有", 0);
        is.put("无", 1);
        is.put("没有", 1);
        is.put("否", 1);
        HashMap<Object, Integer> mp = new HashMap<>();
        mp.put("是", 1);
        mp.put("有", 1);
        mp.put("无", 0);
        mp.put("没有", 0);
        mp.put("否", 0);

        //获取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        List<VehicleEntity> vehicleEntities = new ArrayList<>();
        //第一行表头校验
        String[] headers = {"车主姓名", "身份证号", "性别", "手机号", "联系地址", "车牌号", "车架号", "防盗芯片", "电动机号", "来历承诺书", "车辆类型", "车辆品牌", "区县/辖区", "车身颜色", "购买价格", "购买时间", "新旧程度", "套餐项", "套餐开始时间", "套餐结束时间", "是否有防盗险", "防盗险", "是否有意外险", "意外险", "是否有第三方险", "第三方责任险", "登记人", "职业", "备用电话", "备案时间", "安装网点"};
        Row row0 = sheet.getRow(0);

        //文件非空校验

        //从第二行开始获取数据
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);//获取每一行数据
            Object objs[] = new Object[row.getLastCellNum()];//创建一个与行数对应大小的数组
            //从每一行第一列开始获取数据
            VehicleEntity vehicleEntity = new VehicleEntity();


            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);//获取单元格对象
                objs[cellNum] = getValue(cell);//获取单元格的值填充到上面那个数组对应的索引位置
            }
            vehicleEntity.setCarname(objs[0].toString());
            vehicleEntity.setCarid((objs[1].toString()).replace(".", "").substring(0, 18));
            vehicleEntity.setSex("女".equals(objs[2].toString()) ? 1 : 0);
            vehicleEntity.setMobile(memberFormat((HSSFCell) objs[3]));
            vehicleEntity.setCaraddress(objs[4].toString());
            vehicleEntity.setVinno(objs[5].toString());
            vehicleEntity.setTheftnum(objs[6].toString());
            vehicleEntity.setTagcode(objs[7].toString());
            vehicleEntity.setStartmachine(objs[8].toString());
            vehicleEntity.setIspromise(mp.get(objs[9].toString()));
            vehicleEntity.setLighttypes(type.get(objs[10].toString()));
            vehicleEntity.setVehiclebrand(objs[11].toString());
            vehicleEntity.setNewareaid(findAreaIdbyAreaName(objs[12] + ""));
            vehicleEntity.setCarcolour(hashMap.get(objs[13].toString()));
            vehicleEntity.setBugprice(new BigDecimal(objs[14].toString()));
            System.out.println(objs[15].getClass());
            vehicleEntity.setBugtime(dateFormat((HSSFCell) objs[15]));
            vehicleEntity.setDegree("新".equals(objs[16].toString()) ? 0 : 1);
            vehicleEntity.setMeal_name(objs[17].toString());
            vehicleEntity.setStart_time(dateFormat((HSSFCell) objs[18]));
            vehicleEntity.setEnd_time(dateFormat((HSSFCell) objs[19]));
            vehicleEntity.setTheftdutp((objs[20].toString()));
            vehicleEntity.setAccident((objs[21].toString()));
            vehicleEntity.setThirdduty((objs[22].toString()));
            vehicleEntity.setReger(objs[23].toString());
            vehicleEntity.setVocation(objs[24].toString());
            vehicleEntity.setMobileback(memberFormat((HSSFCell) objs[25]));
            vehicleEntity.setKeeptime(dateFormat((HSSFCell) objs[26]));
            vehicleEntity.setSalesite(objs[27].toString());

            vehicleEntities.add(vehicleEntity);
        }
        for (VehicleEntity entity : vehicleEntities) {

            importVehicle(entity);

        }
    }

    /**
     * d电话号码的转换
     *
     * @param memberCell
     * @return
     */
    private String memberFormat(HSSFCell memberCell) {
        DecimalFormat df = new DecimalFormat("#");

        String mobile = memberCell.toString();
        switch (memberCell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字
                mobile = df.format(memberCell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:// 字符串
                mobile = df.format(Double.parseDouble(memberCell.toString()));
                break;
            default:
                mobile = memberCell.toString();
                break;
        }
        return mobile;
    }

    /**
     * 通过地区名称查id
     *
     * @param name
     * @return
     */
    private String findAreaIdbyAreaName(String name) {
        String s = null;
        try {
            s = jdbcTemplate.queryForObject("SELECT `id` FROM zydzn_country WHERE `name` = ? ", String.class, name);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return s;
    }

    /**
     * 导入数据的插入
     *
     * @param vehicle
     */
    private void importVehicle(VehicleEntity vehicle) {
        vehicle.setId(UUIDUtil.getUUid());

        checkDataValidation(vehicle);
        checkValid(vehicle);

        vehicle.setTs(ToolUtils.getNowTime());
        vehicle.setDr(0);
        // 新增创建人
        vehicle.setCreateid("");

        SafeEntity meal = findSafeByName(vehicle.getMeal_name());

        MealEntity entity = new MealEntity();
        entity.setAccident(vehicle.getAccident());
        entity.setThirdduty(vehicle.getThirdduty());
        entity.setTheftdutp(vehicle.getTheftdutp());
        entity.setStart_time(vehicle.getStart_time());
        entity.setEnd_time(vehicle.getEnd_time());
        entity.setId(UUIDUtil.getUUid());
        entity.setCreate_time(ToolUtils.getNowTime());
        entity.setHas_accient(meal.getHas_accient());
        entity.setHas_thirdduty(meal.getHas_third());
        entity.setHas_theftdutp(meal.getHas_theft());
        entity.setMeal_money(meal.getSalemny());
        entity.setMeal_name(meal.getSalename());
        entity.setVehicle_id(vehicle.getId());
        entity.setMeal_id(meal.getId());

        mapper.insertSelective(vehicle);
        mealService.addMeal(entity);
    }

    @Autowired
    private SafeEntityMapper safeEntityMapper;

    private SafeEntity findSafeByName(String meal_name) {
        SafeEntityExample example = new SafeEntityExample();
        example.createCriteria().andSalenameEqualTo(meal_name).andDrEqualTo(0);
        List<SafeEntity> safeEntities = safeEntityMapper.selectByExample(example);
        if (safeEntities.isEmpty()) {
            return new SafeEntity();
        }
        return safeEntities.get(0);
    }

    /**
     * 将表格的HSSFCell转换为时间格式
     *
     * @param obj
     * @return
     */
    private String dateFormat(HSSFCell obj) {
        Date date = obj.getDateCellValue();
        return new SimpleDateFormat("yyyy-MM-dd").format(date);
    }
}